package com.lovo.dao.impl;

import com.lovo.bean.Student;
import com.lovo.dao.BaseDAO;
import com.lovo.dao.StudentDAO;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@Repository
public class StudentDAOImpl extends BaseDAO implements StudentDAO {
    @Override
    public List listStudent() {
        Connection con = this.getConnection();
        PreparedStatement statement = null;
        ResultSet rs = null;
        String sql = "select * from t_student";
        List list = new ArrayList();
        try {
            statement = con.prepareStatement(sql);
            rs = statement.executeQuery();
            while(rs.next()){
                Student student = new Student();
                student.setId(rs.getInt("s_id"));
                student.setName(rs.getString("s_name"));
                student.setGender(rs.getString("s_gender"));
                student.setAge(rs.getInt("s_age"));
                list.add(student);
            }
        } catch (SQLException e) {
        }finally {
            close(con,statement,rs);
        }
        return list;
    }

    @Override
    public Student getById(int id) {
        Connection con = this.getConnection();
        PreparedStatement statement = null;
        ResultSet rs = null;
        String sql = "select * from t_student where s_id=?";
        List list = new ArrayList();
        Student student = new Student();
        try {
            statement = con.prepareStatement(sql);
            statement.setInt(1,id);
            rs = statement.executeQuery();
            if(rs.next()){
                student.setId(rs.getInt("s_id"));
                student.setName(rs.getString("s_name"));
                student.setGender(rs.getString("s_gender"));
                student.setAge(rs.getInt("s_age"));
            }
        } catch (SQLException e) {
        }finally {
            close(con,statement,rs);
        }
        return student;
    }

    @Override
    public void insert(Student student) {
        Connection con = this.getConnection();
        PreparedStatement statement = null;

        String sql = "insert into t_student(s_name,s_age,s_gender) values(?,?,?)";
        try {
            statement = con.prepareStatement(sql);
            statement.setString(1,student.getName());
            statement.setInt(2,student.getAge());
            statement.setString(3,student.getGender());
            statement.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            close(con,statement);
        }
    }

    @Override
    public void update(Student student) {
        Connection con = this.getConnection();
        PreparedStatement statement = null;

        String sql = "update t_student set s_name=?,s_age=?,s_gender=? where s_id=?";
        try {
            statement = con.prepareStatement(sql);
            statement.setString(1,student.getName());
            statement.setInt(2,student.getAge());
            statement.setString(3,student.getGender());
            statement.setInt(4,student.getId());
            statement.executeUpdate();

        } catch (SQLException e) {
        }finally {
            close(con,statement);
        }
    }

    @Override
    public void delete(int id) {
        Connection con = this.getConnection();
        PreparedStatement statement = null;

        String sql = "delete from t_student where s_id=?";
        try {
            statement = con.prepareStatement(sql);
            statement.setInt(1,id);
            statement.executeUpdate();

        } catch (SQLException e) {
        }finally {
            close(con,statement);
        }
    }
}
